﻿
CREATE PROCEDURE [dbo].[proc_InOutSide_Customer_GetEmployeeList]
(
	@UserName nvarchar(50)
)
As
begin
	declare @companyid int,@Days int,@DepartmentId int
	Declare @Extract int

	EXEC @companyid=proc_Company_GetID @UserName,@companyid		--该用户的单位ID

	set @DepartmentId = (Select DepartmentId From Employee Where UserName=@UserName)--部门Id
	Set @Days = (Select ProtectDays From Department Where id=@DepartmentId)--系统保护天数

	--已保护,但过了保护日期仍未签单的,自动踢入公库并且5日内不能再次被申领(根据UpTime)
	Update Customer Set ProtectId=1,ProtectName=NULL,ProtectDate=NULL,UpTime=GetDate() Where ProtectName=@UserName And DateDIFF(Day,ProtectDate,GetDate())>@Days And companyId=@companyid

	--提取剩下的客户
	Select 
		Id,
		delFlag,
		CustomerId,
		CustomerName,
		Project,
		ProtectId,
		IsNull((Select Title From Customer_Source Where Id=C.SourceId),'...') As Source,
		IsNull(ProtectDate,'1900-01-01') As ProtectDate,
		IsNull(TrackDate,'1900-01-01') As TrackDate,
		IsNull((@Days-datediff(day,Isnull(ProtectDate,GetDate()),GetDate())),0) As Countdown,
		IsNull((Select Title From Customer_Industry Where Id=C.IndustryOneId),'...') As IndustryOne,
		IsNull((Select Title From Customer_Industry Where Id=C.IndustryTwoId),'...') As IndustryTwo,
		IsNull((Select Title From Customer_Type Where Id=C.TypeId),'...') As Type,
		CreateDate,
		ISNULL((SELECT ios.Out_Date
		          FROM InOutSide ios WHERE ios.InSide=@UserName 
		          AND ios.CusId=c.CustomerId AND ios.Out_Date>c.ProtectDate),'1900-01-01') AS Out_Date,
		ISNULL((SELECT ios.Id
		          FROM InOutSide ios WHERE ios.InSide=@UserName 
		          AND ios.CusId=c.CustomerId AND ios.Out_Date>c.ProtectDate),0) AS InOutSideId
	From Customer C
	Where ProtectName=@UserName and Audit=1 and Companyid=@companyid Order by ProtectDate Desc,ProtectId ASC ,UpTime ASC
End